package honest.playmusic.sqlite;

import honest.playmusic.bean.Music;
import honest.playmusic.data.ConfigData;
import honest.playmusic.exception.FileCorruptException;
import honest.playmusic.util.MusicManager;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.ConfigurationException;


/**
 * 歌曲数据库操作
 * @author 李跃东
 * @mail androiddevelop@qq.com
 * @date 2013-11-8
 */
public class MusicDb {
	private String directoryPath;
	private List<Music> list=new ArrayList<Music>();
	private ResultSet res=null;
	private Statement stmt=null;
	private Connection conn=null;
	private Music music=new Music();
	private static int num=0;

	/**
	 * 更新操作必须实现该构造方法
	 * @param directoryPath 音乐库目录
	 */
	public MusicDb(String directoryPath){
		this.directoryPath=directoryPath;
	}


	/**
	 * 更新操作
	 * @throws ConfigurationException  配置文件错误导致的文件不存在等问题
	 * @throws FileCorruptException 文件损坏异常
	 */
	public void update() throws ConfigurationException, FileCorruptException{
		File dir=new File(directoryPath);
		if(!dir.exists()||!dir.isDirectory()){
			throw new ConfigurationException("配置文件错误");
		}
		connect();
		System.out.println("开始扫描音乐:");
		num=0;
		deleteAll();
		start(dir);
		System.out.println("\n一共添加音乐数量: "+num);
		close();
	}


	/**
	 * 进行文件遍历，将符合要求信息插入数据库
	 * @param dir 文件目录
	 * @throws FileCorruptException Mp3文件已经损坏
	 */
	private void start(File dir) throws FileCorruptException{
		for(File f:dir.listFiles()){
			if(f.isFile()){
				if(f.getName().endsWith(".mp3")){
					music.setMusicAbsolutePath(f.getAbsolutePath());
					String name=f.getName();
					name=name.substring(0, name.length()-4);
					int index=name.indexOf("-");
					if(index==-1)
						music.setMusicAuthor(f.getParentFile().getName());
					else
						music.setMusicAuthor(name.substring(0,index).trim());
					music.setMusicName(name.substring(index+1).trim());
					long length=MusicManager.getMusicTime(f);
					if(length==-1){
						throw new FileCorruptException("文件"+f.getName()+"已损坏,目录:"+f.getParent());
					}else{
						music.setMusicTime(length);
						music.setMusicFrameNumber((int)(length/26));
						insert(music);
					}
				}
			}else{
				start(f);
			}
		}
	} 

	/**
	 * 插入数据库
	 * @param music 单首音乐
	 */
	private void insert(Music music){
		String sql="insert into music values('"+music.getMusicName()+"'," +
				"'"+music.getMusicAuthor()+"'," +
				"'"+music.getMusicAbsolutePath()+"'," +
				"'"+music.getMusicTime()+"'," +
				"'"+music.getMusicFrameNumber()+"')";
		try {
			stmt.executeUpdate(sql);
			num++;
			System.out.print(".");
		} catch (SQLException e) {
			System.out.println(music.getMusicName());
			System.out.println(sql);
			e.printStackTrace();
		}
	}

	/**
	 * 查询数据库
	 * @return
	 */
	public  List<Music> query(String searchWord){
		connect();
		String sql="select * from music where musicName like '%"+searchWord+"%' or musicAuthor like '%"+searchWord+"%'";
		try {
			res=stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		list.clear();
		try {
			while(res.next()){
				music=new Music();
				music.setMusicAbsolutePath(res.getString("musicAbsolutePath"));
				music.setMusicAuthor(res.getString("musicAuthor"));
				music.setMusicName(res.getString("musicName"));
				music.setMusicTime(Long.valueOf(res.getString("musicTime")));
				music.setMusicFrameNumber(Integer.valueOf(res.getString("musicFrameNumber")));
				list.add(music);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
		}
		return list; 
	}

	/**
	 * 查询歌手有多少首歌曲
	 * @return
	 */
	public int queryAuthor(String author){
		connect();
		String sql="select * from music where musicAuthor= '"+author+"'";
		try {
			res=stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		int num=0;
		try {
			while(res.next()){
				num++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close();
		}
		return num; 
	}



	/**
	 * 删除所有信息
	 */
	public void deleteAll(){
		String sql="delete from music";
		try {
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 数据库连接
	 */
	public void connect(){
		try {
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite://"+ConfigData.DB_PATH);  
			stmt = conn.createStatement();   
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}  
	}

	/**
	 * 关闭连接
	 */
	private void close(){
		try {
			if(stmt!=null)
				stmt.close();
			if(stmt!=null)
				conn.close();   
		} catch (SQLException e) {
			e.printStackTrace();
		}  
	}
}
